import sqlite3
import datetime
from flask import Flask, request, session, g, redirect, url_for, abort, render_template, flash
from contextlib import closing

#config
DATABASE='/var/sqlite3/flask.db'
DEBUG=True
SECRET_KEY='development key'
USERNAME='admin'
PASSWORD='admin'
TARGETS=['target1', 'target2', 'target3', 'target4', 'target5']

myapp=Flask(__name__)
myapp.config.from_object(__name__)

def connect_db():
        return sqlite3.connect(myapp.config['DATABASE'])

def init_db():
        with closing(connect_db()) as db:
                with myapp.open_resource('init.sql') as fsql:
                        db.cursor().executescript(fsql.read())
                db.commit()


@myapp.before_request
def before_request():
        g.db=connect_db()

@myapp.teardown_request
def teardown_request(exception):
        g.db.close()


@myapp.route('/')
def home():
        print "/home============================================================="

        #entries index
        ret=g.db.execute('select * from entries order by createtime desc')
        entries=[dict(id=row[0], title=row[1], text=row[2], createtime=row[3]) for row in ret.fetchall()]
        print "[entries]:\n", entries

        #targetlists index
        targetlists={}
        for entry in entries:
                print "[entry]:\n", entry
                print "type entry['id']\n", type(entry['id'])
                arr=[]
                ret=g.db.execute('select * from r_entry_target where entry_id=?', [str(entry['id'])])
                for i in ret.fetchall():
                        print i
                        arr.append(i[2])
                print arr
                targetlists[entry['id']]=arr
        print "{targetlists}:\n", targetlists

        return render_template('home.html', fulltargets=TARGETS, entries=entries, targetlists=targetlists)

@myapp.route('/indexselection')
def indexselection():
        print "/indexselection==============================================================="

        error=None

        #get parameter
        date_start=request.args.get('date_start', '')
        date_end=request.args.get('date_end', '')
        targets=request.args.get('targets', '')
        keywords=request.args.get('keywords', '')
        print 'date_start: ', date_start
        print 'date_end: ', date_end
        print 'targets: ', targets
        print 'keywords: ', keywords

        #check parmeter
        if date_start=="":
                date_start="1970-01-01"
        if date_end=="":
                cur_time=datetime.datetime.now()
                date_end=cur_time.strftime("%Y-%m-%d")

        if date_start>date_end:
                print "time range beyond limit"
                error='time range beyond limit'

        date_start=date_start+' 00:00:00'
        date_end=date_end+' 23:59:59'
        print 'date_start: ', date_start
        print 'date_end: ', date_end

        #select with parameter
        #if targets!="":
                #for 
                #cut_sql=
        #if keywords!="":

        ret=g.db.execute('select * from entries where createtime between datetime(?) and datetime(?)', [date_start, date_end])
        entries=[dict(id=row[0], title=row[1], text=row[2], createtime=row[3]) for row in ret.fetchall()]
        print "[entries]:\n", entries

        #targetlists index
        targetlists={}
        for entry in entries:
                print "[entry]:\n", entry
                print "type entry['id']\n", type(entry['id'])
                arr=[]
                ret=g.db.execute('select * from r_entry_target where entry_id=?', [str(entry['id'])])
                for i in ret.fetchall():
                        print i
                        arr.append(i[2])
                print arr
                targetlists[entry['id']]=arr
        print "{targetlists}:\n", targetlists

        return render_template('indexselection.html', date_start=date_start, date_end=date_end, targets=targets, keywords=keywords, fulltargets=TARGETS, entries=entries, targetlists=targetlists, error=error)

@myapp.route('/targetindex')
def targetindex():
        print "/targetindex============================================================="

        #get target_name
        target_name=request.args.get('target_name', '')
        print target_name

        #select entries with target_name
        ret=g.db.execute('select entry_id from r_entry_target where target_name=? order by entry_id desc', [target_name])

        #entries index
        entries=[]
        for i in ret.fetchall():
                print i
                print i[0]
                ret=g.db.execute('select * from entries where id=?', [i[0]])
                row=ret.fetchone()
                entry=dict(id=row[0], title=row[1], text=row[2], createtime=row[3])
                entries.append(entry)
        print "[entries]:\n", entries

        #targetlists index
        targetlists={}
        for entry in entries:
                print "[entry]:\n", entry
                print "type entry['id']\n", type(entry['id'])
                arr=[]
                ret=g.db.execute('select * from r_entry_target where entry_id=?', [str(entry['id'])])
                for i in ret.fetchall():
                        print i
                        arr.append(i[2])
                print arr
                targetlists[entry['id']]=arr
        print "{targetlists}:\n", targetlists

        return render_template('targetindex.html', entries=entries, targetlists=targetlists)

@myapp.route('/login', methods=['GET', 'POST'])
def login():
        error=None
        if request.method == 'POST':
                if   request.form['username'] != myapp.config['USERNAME']:
                        error='Invalid username'
                elif request.form['password'] != myapp.config['PASSWORD']:
                        error='Invalid password'
                else:
                        session['logged_in']=True
                        flash('You were logged in!')
                        return redirect(url_for('home'))
        return render_template('login.html', error=error)

@myapp.route('/logout')
def logout():
        session.pop('logged_in', None)
        flash('Your were logged out!')
        return redirect(url_for('home'))

@myapp.route('/new')
def new():
        if not session.get('logged_in'):
                abort(401)
        return render_template('new.html', fulltargets=TARGETS)

@myapp.route('/add', methods=['GET', 'POST'])
def add():
        print "/add============================================================="
        if not session.get('logged_in'):
                abort(401)

        #insert into entries
        print "title: ",request.form['title']
        print "text: ", request.form['text']
        print "targetlist: ", request.form['targetlist']
        g.db.execute('insert into entries(title, text) values(?,?)', [request.form['title'], request.form['text']])
        g.db.commit()

        #get last auto increment id !must in the same db connection
        ret=g.db.execute('select last_insert_rowid();')
        entry_id=ret.fetchone()[0]
        print entry_id

        #get targetlist
        targetlist=request.form['targetlist'].split(';')

        #insert into r_entry_target
        for target in targetlist:
                print target
                if target:
                        g.db.execute('insert into r_entry_target(entry_id, target_name) values(?,?)', [entry_id, target])
                        g.db.commit()
                        print "inserted"

        flash('You were posted new entry recorder')
        return redirect(url_for('home'))

@myapp.route('/detail')
def detail():
        print "/detail=========================================================="
        if not session.get('logged_in'):
                abort(401)

        #get entry id
        entry_id=request.args.get('id', '')
        print entry_id

        #select from entries
        ret=g.db.execute('select * from entries where id=?', [entry_id])
        row=ret.fetchone()
        print row
        entry=dict(id=row[0], title=row[1], text=row[2], createtime=row[3])
        print entry

        #select from r_entry_target
        targetlist=[]
        ret=g.db.execute('select * from r_entry_target where entry_id=?', [entry_id])
        for i in ret.fetchall():
                targetlist.append(i[2])
        return render_template('detail.html', entry=entry, targetlist=targetlist)

@myapp.route('/edit')
def edit():
        print "/edit============================================================="
        if not session.get('logged_in'):
                abort(401)

        #get entry id
        entry_id=request.args.get('id', '')
        print entry_id

        #select from entries
        ret=g.db.execute('select * from entries where id=?', [entry_id])
        row=ret.fetchone()
        print row
        entry=dict(id=row[0], title=row[1], text=row[2], createtime=row[3])
        print entry

        #select from r_entry_target
        targetlist=[]
        ret=g.db.execute('select * from r_entry_target where entry_id=?', [entry_id])
        for i in ret.fetchall():
                targetlist.append(i[2])
        return render_template('edit.html', fulltargets=TARGETS, entry=entry, targetlist=targetlist)

@myapp.route('/update', methods=['GET', 'POST'])
def update():
        print "/update============================================================="
        if not session.get('logged_in'):
                abort(401)

        #get entry_id
        entry_id=request.args.get('id', '')
        print entry_id
        
        #update entries
        g.db.execute('update entries set title=?, text=? where id=?', [request.form['title'], request.form['text'], entry_id])
        g.db.commit()

        #get targetlist
        targetlist=request.form['targetlist'].split(';')

        #delete from r_entry_target
        g.db.execute('delete from r_entry_target where entry_id=?', [entry_id])
        g.db.commit()

        #insert into r_entry_target
        for target in targetlist:
                print target
                if target:
                        g.db.execute('insert into r_entry_target(entry_id, target_name) values(?,?)', [entry_id, target])
                        g.db.commit()
                        print "inserted"

        flash('You were updated '+entry_id+'!')
        return redirect(url_for('home'))

@myapp.route('/delete')
def delete():
        print "/delete============================================================="
        if not session.get('logged_in'):
                abort(401)

        #get entry id
        entry_id=request.args.get('id', '')
        print entry_id

        #delete from entries
        g.db.execute('delete from entries where id=?', [entry_id])
        g.db.commit()

        #delete from r_entry_target
        g.db.execute('delete from r_entry_target where entry_id=?', [entry_id])
        g.db.commit()

        flash('You were deleted '+entry_id+'!')
        return redirect(url_for('home'))

        
if __name__=='__main__':
#        print myapp.config
#        connect_db()
        myapp.run(debug=True, host='0.0.0.0', port=5001)
